const express = require("express");
const multer = require('multer');
const fs =require('fs');
const path = require('path');
const router = express.Router();
const pool = require("../pool.js");
const utils = require("../utils/index.js");
const request = require('request')
const {systemSettings} =require("../utils/menuString");
const { pushClearanceStatus } = require('./clearanceStatus.js');

const APP_URL = 'https://api.weixin.qq.com/sns/jscode2session'
const { v4: uuidv4 } = require('uuid');
const { sedMessage } = require("../utils/index.js");
const pushPaymentStatus = require('./ydapi.js')

const dayjs = require('dayjs');
var WXBizDataCrypt = require('../utils/WXBizDataCrypt')

const CHANGE_TIME = '2024-06-05 10:00:00'; 
// const CHANGE_TIME = '2024-06-03 10:00:00'; 


//支付
const WxPay = require('wechatpay-node-v3');
let cert = fs.readFileSync('pem/apiclient_cert.pem')
let PRIMARY_PEM = fs.readFileSync('pem/apiclient_key.pem')
const pay = new WxPay({
    appid: 'wxff7770c76ae1c75e',
    mchid: '1709096771',
    publicKey: cert,
    privateKey: PRIMARY_PEM
})




//支付接口
router.post('/wxpay', async (req, res) => {
    let { Goods, openid,orderId ,phone} = req.body
    let order_out_id=orderId;
    const params = {
        description: Goods.text, // 订单描述
        out_trade_no: order_out_id, // 订单号，一般每次发起支付都要不一样，可使用随机数生成
        notify_url: 'https://applet.laiyifenhyqy.com/api/syn/notify_url', 
        amount: {
          total: Goods.total_fee, // 支付金额，单位为分
        },
        payer: {
          openid: openid, // 微信小程序用户的openid，一般需要前端发送过来
        },
        scene_info: {
          payer_client_ip: 'ip', // 支付者ip，这个不用理会也没有问题
        },
    };
      const result = await pay.transactions_jsapi(params);
      res.send(utils.returnData({data: result}));
})
  //回调
  router.post('/notify_url',(req,res) => {
    const query = req.query
    console.log('进入回调');
    let { ciphertext, associated_data, nonce } = req.body.resource;
    // 解密回调信息
    const result = utils.decodeByAES(ciphertext, systemSettings.wxpay.apiv3, nonce, associated_data);
    console.log(3333);
    console.log(555555,result);
    let data=JSON.parse(result);
    // 拿到订单号
    let out_trade_no = data.out_trade_no;
    if (data.trade_state == "SUCCESS") {
        //更改订单状态
        let sql = `UPDATE t_new_order 
            SET state = CASE 
              WHEN goodsIsOnlyCoupon = 1 THEN 3 
              ELSE 1 
            END 
            WHERE order_id = ?;`;
        pool.query(sql, [out_trade_no],(err, result) => {
            if (err) return res.send(utils.returnData({code: -1,err,req}));
            res.send(utils.returnData({data: {}}));
        });
    }else{
      //更改订单状态
        console.log(2,data);
    }
})
//申请退款
// router.post("/upOrderStatusByItem", async (req, res) => {
//     let obj=req.body;
//     console.log(obj);
//     let sql = `UPDATE  orders SET status=2   WHERE order_id=?`;
//     pool.query(sql,[obj.orderId],(err, result) => {
//         if (err) return res.send(utils.returnData({code: -1,err,req}));
//         res.send(utils.returnData({data: result}));
//     });
// });

//退款
router.post('/wx_refund',async (req,res)=>{
    console.log(120,req.body.money);
    let order_no=utils.getOrderNo("yyyyMMddhhmmssS")+Math.floor(Math.random() * 89) + 10;
    let rNum = order_no//自己生成退款单号
    let params = {
        out_trade_no: req.body.out_trade_no,//原订单号
        out_refund_no: rNum,
        notify_url:'https://applet.laiyifenhyqy.com/api/syn/notify_refund',
        amount:{
            refund: Math.ceil(Number(req.body.money)),
            total: Math.ceil(Number(req.body.money)),
            // refund: Math.ceil(1),
            // total: Math.ceil(1),
            currency: 'CNY'
        }
    } 
    console.log(130);
    const result = await pay.refunds(params);
    console.log(9999,result);
    res.send(utils.returnData({data: {}}));
    
 });

//微信支付退款回调通知
router.post('/notify_refund', async (req, res) => {
    try {
        let {
            ciphertext,
            associated_data,
            nonce
        } = req.body.resource;
        // 解密回调信息
        const result = utils.decodeByAES(ciphertext, systemSettings.wxpay.apiv3, nonce, associated_data);
        console.log("退款回调");
        console.log(888,result);
        let data=JSON.parse(result);
        let out_trade_no = data.out_trade_no;
        if (data.refund_status == "SUCCESS") {
            console.log(88,data);
            let sql = `update t_new_order  set state=5  WHERE order_id =? `;
            pool.query(sql, [out_trade_no],(err, result) => {
                if (err) return res.send(utils.returnData({code: -1,err,req}));
                res.send(utils.returnData({data: {}}));
            });
        }else{
            console.log(2,data);
        }
        
    } catch (error) {
        res.status(500);
        res.send({
            code: 'FAIL',
            message: "失败",
        });
    }
});









/**
 * @swagger
 * components:
 *  securitySchemes:
 *   ApiKeyAuth:
 *     type: apiKey 
 *     in: header 
 *     name: Authorization
 */



/**
 * @swagger
 * /syn/lyf/order:
 *   post:
 *     tags:
 *       - 外部推送-订单信息
 *     summary: 推送订单
 *     security:
 *       - ApiKeyAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
//推送订单信息
router.post("/lyf/order", async (req, res) => {
    let headersAuth=req.headers.authorization;
    console.log(999,headersAuth);
    let auth = "Bearer SHIXIU_VzV4op4jprvq5TWdV1Xei54yu9e0gExzxYnNjOYB0rJCHB9i";
    if (auth!==headersAuth) {
       return res.send(utils.returnData({code: -1}));
    }
    let obj=req.body;
    let bodyJson=obj.contractRoot.body;
    console.log(888,bodyJson);


    let phone=bodyJson.serverNum;
    console.log(999,phone);
    //查询用户
    let userId=await utils.getTUserId({phone:phone,res,req});

    //查询产品Id
    let skuId=bodyJson.skuId;
    let skuCode=bodyJson.skuCode;
    let skuName=bodyJson.skuName;
    let msgName=null;
        if(skuCode=='lyf01'){
            skuName="来伊份零食畅享盲盒月卡";
            msgName="零食畅享盲盒";
        }
        if(skuCode=='lyf02'){
            skuName="来伊份零食精选盲盒月卡";
            msgName="零食精选盲盒";
        }
        if(skuCode=='lyf03'){
            skuName="来伊份零食十全大礼包";
            msgName="十全大礼包";
        }
    


    let productId=await utils.getProductId({skuId,skuCode,skuName,res,req});


    let orderId=bodyJson.orderItemId;
    let extCode=bodyJson.extCode;
    let quantity=bodyJson.quantity;
    let rightsId=bodyJson.rightsId;
    let price=bodyJson.price;
    let dateString=bodyJson.createTime.slice(0, 4)+'-'+bodyJson.createTime.slice(4, 6)+'-'+bodyJson.createTime.slice(6, 8)+' '+bodyJson.createTime.slice(8, 10)+':'+bodyJson.createTime.slice(10, 12)+':'+bodyJson.createTime.slice(12, 14);
    var createTime = new Date(dateString);
    console.log(999,createTime);
    let order_serial='';
    for (let i = 0; i < 12; i++) {
      order_serial += Math.floor(Math.random() * 10); // 生成0到9之间的随机数
    }
    let createTimestamp = new Date(dateString).getTime();
    console.log(111,createTimestamp);
    let targetTimestamp = new Date(CHANGE_TIME).getTime();
    console.log(222,targetTimestamp);
    let timeDiff = createTimestamp - targetTimestamp;
    console.log(333,timeDiff);
    let msgId='';
    if(timeDiff > 0  && msgName!=null){
      console.log(777,timeDiff);
      //非历史订单
      let mesData=await utils.sedHttpMessage(phone,msgName);
      let mesData2=await utils.sedHttpSpringMessage(phone);
      msgId=mesData.msgId;
    }

    //插入订单
    let {total}=await utils.getSum({name:"t_order",where:`WHERE order_id="${orderId}"`,res,req});
    if(total==0){
      let sql ="INSERT INTO  t_order(source, order_id, phone, receive_time, ext_code, quantity,rights_id, price, product_id, sku_id, sku_code, sku_name, user_id, user_consignee_id, express_company, tracking_number, state, sms_num, create_time, update_time, created_at,order_serial,msgId) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
      pool.query(sql, ["来伊份", orderId, phone, null,extCode, quantity,rightsId,price, productId, skuId, skuCode, skuName, userId, 0, '', '', 0, 0, createTime,new Date(),new Date(),order_serial,msgId],(err, result) => {
          if (err) return res.send(utils.returnData({code: -1,err,req}));
          res.send(utils.returnData({data: {}}));
      });
    }else{
      res.send(utils.returnData({data: {}}));
    }
    
});

//重新推送短信
router.post('/resendMsg',async (req, res) => {
  console.log(777,req.body);
  let orderId=req.body.orderId;
  let phone=req.body.phone;
  let skuName=req.body.skuName.replace('来伊份','').replace('月卡','');
  if(skuName.indexOf("十全大礼包") != -1){
    skuName="十全大礼包";
  }
  var messData=await utils.sedHttpMessage(phone,skuName);
  let mesData2=await utils.sedHttpSpringMessage(phone);
  let sql2 ="update t_order  set  update_time=?,msgId=?,msgNum=ifnull(msgNum,0)+1,massage_status=250  where  order_id=?";
    pool.query(sql2, [new Date(),messData.msgId,orderId],(err2, result2) => {
      if (err2) return res.send(utils.returnData({code: -1,err2,req}));
      res.send(utils.returnData({data: result2}));
    });
  
})



//修改短信状态
router.post('/updatetOrderSms',async (req, res) => {
  console.log(777,req.body);
  let orderId=req.body.orderId;
  let phone=req.body.phone;
  let sql2 ="update t_order_sms  set  status=?,result=?,updated_at=?  where  t_order_id=?";
  pool.query(sql2, [messData.code,messData.msg,new Date(),orderId],(err2, result2) => {
      if (err2) return res.send(utils.returnData({code: -1,err2,req}));
      res.send(utils.returnData({data: result2}));
  });
})




//小程序相关

/**
 * @swagger
 * /syn/getOpenid:
 *   post:
 *     tags:
 *       - 小程序-主页
 *     summary: 授权/获取openid
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
router.post('/getOpenid', (req, res) => {
  console.log(777,req.body);
    const js_code = req.body.code
    const ART_APP_ID = systemSettings.wxpay.APPID//AppID(小程序ID)
    const ART_APP_SECRET =systemSettings.wxpay.ART_APP_SECRET //AppSecret(小程序密钥)	
    if (js_code) {
      request(`${APP_URL}?appid=${ART_APP_ID}&secret=${ART_APP_SECRET}&js_code=${js_code}&grant_type=authorization_code`, (error, response, body) => {
        if (error) throw error
        if (response.statusCode === 200) {
          const bodyInfo = JSON.parse(body)
          console.log(1234,bodyInfo);
          const openId = bodyInfo.openid
          console.log(8989,openId);
          res.send(utils.returnData({data: openId}));
        } else {
            return res.send(utils.returnData({code: -1,err,req}));
        }
      })
    }
  })

  router.post('/getUserPhone', (req, res) => {
  console.log(777,req.body);
    const js_code = req.body.code
    const ART_APP_ID = systemSettings.wxpay.APPID//AppID(小程序ID)
    const ART_APP_SECRET =systemSettings.wxpay.ART_APP_SECRET //AppSecret(小程序密钥)	
    if (js_code) {
      request(`${APP_URL}?appid=${ART_APP_ID}&secret=${ART_APP_SECRET}&js_code=${js_code}&grant_type=authorization_code`, (error, response, body) => {
        if (error) throw error
        if (response.statusCode === 200) {
          const bodyInfo = JSON.parse(body)
          var sessionKey = bodyInfo.session_key
          var pc = new WXBizDataCrypt(ART_APP_ID, sessionKey)
          var data =pc.decryptData(req.body.encryptedData, req.body.iv)
          data.openId=bodyInfo.openid;
          //获取手机号，通过手机号查询用户user_vip表，查询用户vip权益
          let sql = "SELECT * FROM user_vip WHERE phone=?";
          console.log(1234,data.phoneNumber);
          pool.query(sql, [data.phoneNumber], (err, result) => {
            if (err) return res.send(utils.returnData({code: -1,err,req}));
            console.log(1234,result);
            data.vip=result[0];
          });
          res.send(utils.returnData({data: data}));
        } else {
            return res.send(utils.returnData({code: -1,err,req}));
        }
      })
    }
  })

  //获取用户会员信息的接口
  //过期了就无法查出来
  router.post('/getUserVip', (req, res) => {
    console.log(777,req.body);
    let phone=req.body.phone;
    let sql = "SELECT * FROM user_vip left join vip_content on user_vip.vip_type=vip_content.id WHERE phone=? AND (user_vip.vip_expiration_date IS NULL OR user_vip.vip_expiration_date >= NOW())";
    pool.query(sql, [phone], (err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
    });
  })

/**
 * @swagger
 * /syn/login/state:
 *   post:
 *     tags:
 *       - 小程序
 *     summary: 获取登录状态
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
router.get("/login/state", async (req, res) => {
  var openId=req.query.openId;
  console.log(openId);
  let sql = "SELECT phone,uid,id FROM t_user WHERE open_id=?";
  pool.query(sql, [openId], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
  });

});


/**
 * @swagger
 * /syn/login/validcode:
 *   post:
 *     tags:
 *       - 小程序
 *     summary: 获取登录验证码
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.get("/login/validcode", async (req, res) => {
  var phone=req.query.phone;
  console.log(phone);
  var http = require('http');  
  var sha1=require('sha1');
  var qs = require('querystring'); 
  var Appkey='13a4c1d079b489504c896be4c0abe0da';
  var Nonce="asd";
  var CurTime=2221222;
  var AppSecret='d5abccde1e8f';
  var str=AppSecret + Nonce + CurTime;
  var CheckSum=sha1(str);
  var code='';
  for (let i = 0; i < 4; i++) {
    code += Math.floor(Math.random() * 10); // 生成0到9之间的随机数
  }
    
  var post_data = {  
      templateid:22533366,
      mobile:phone,
      params:'',
      codeLen:4,
      authCode:code
  };
    
    
  var content = qs.stringify(post_data); //序列化 a=c&name=d  并且将中文字符进行urlencode 编码进行传输
    console.log(content)
  var options = {  
      hostname: 'api.netease.im',  
    //port:443,
      path: '/sms/sendcode.action?'+content,  
      method: 'POST',  
      headers: {  
      'AppKey'		: Appkey,
      'Nonce'			: Nonce,
      'CurTime'		: CurTime,
      'CheckSum'		: CheckSum,
          'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
        
      }  
  };  
  var req = http.request(options, function (result) {  
      console.log('STATUS: ' + result.statusCode);  
      result.setEncoding('utf8');  
      result.on('data', function (chunk) {  
          console.log('BODY: ' + chunk); 
          let data=JSON.parse(chunk)
          console.log(666,data.code);
          if(data.code==200){
            console.log('短信发送成功');
            res.send(utils.returnData({code:1,data:{} }));
          }else{
            console.log('短信发送失败');
            console.log(data.msg);
            res.send(utils.returnData({code:0,data:data.msg}));
          }
      });  
  });  
    
  req.on('error', function (e) {  
      console.log('problem with request: ' + e.message);  
  });  
  req.end();
});
// router.get("/login/validcode", async (req, res) => {
//   var phone=req.query.phone;
//   console.log(phone);
//   var code='';
//   for (let i = 0; i < 4; i++) {
//     code += Math.floor(Math.random() * 10); // 生成0到9之间的随机数
//   }
//   var messData=await utils.sedLoginMessage(phone,code);
//   utils.storePhoneVerificationCode(phone,code);
//   console.log('发送登录短信验证码：'+code);
//   const retrievedCode = utils.retrievePhoneVerificationCode(phone);
//   console.log(123,retrievedCode); 
//   res.send(utils.returnData({code:1,data:{} }));
// });



//关联用户的openId

 router.post("/setUser", async (req, res) => {
  let obj=req.body;
  let {total}=await utils.getSum({name:"t_user",where:`WHERE phone  ="${obj.phone}"`,res,req});
  if(total==0){
    let uid=uuidv4();
      let sql ="INSERT INTO  t_user(source,phone, uid, open_id, created_at) VALUES (?,?,?,?,?)";
      pool.query(sql, ["来伊份",obj.phone,uid, obj.openId ,new Date()],(err, result2) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({code:1,data:{code:1} }));
      });
    }else{
      let sql ="update t_user  set  open_id=?   where phone=?";
      pool.query(sql, [obj.openId,obj.phone],(err, result2) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({code:1,data:{code:2} }));
      });
    }
 })

/**
 * @swagger
 * /syn/login:
 *   post:
 *     tags:
 *       - 小程序
 *     summary: 登录
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.get("/login", async (req, res) => {
    let obj=req.query;
    var phone=obj.phone;
    var code=obj.code;
    var openId=obj.openId;
    var uid=uuidv4();
    let {total}=await utils.getSum({name:"t_user",where:`WHERE phone  ="${phone}"`,res,req});
    console.log(phone+code);
    var http = require('http');  
    var sha1=require('sha1');
    var qs = require('querystring'); 
    var Appkey='13a4c1d079b489504c896be4c0abe0da';
    var Nonce="asd";
    var CurTime=2221222;
    var AppSecret='d5abccde1e8f';
    var str=AppSecret + Nonce + CurTime;
    var CheckSum=sha1(str);
    var post_data = {  
        mobile:phone,
        code:code
    };
    var content = qs.stringify(post_data); //序列化 a=c&name=d  并且将中文字符进行urlencode 编码进行传输
      console.log(content)
    var options = {  
        hostname: 'api.netease.im',  
      //port:443,
        path: '/sms/verifycode.action?'+content,  
        method: 'POST',  
        headers: {  
        'AppKey'		: Appkey,
        'Nonce'			: Nonce,
        'CurTime'		: CurTime,
        'CheckSum'		: CheckSum,
            'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
          
        }  
    };  
    var req = http.request(options, function (result) {  
        console.log('STATUS: ' + result.statusCode);  
        result.setEncoding('utf8');  
        result.on('data', function (chunk) {  
            console.log('BODY: ' + chunk); 
            let data=JSON.parse(chunk)
            console.log(999,data.code);
            console.log(888,uid);
            if(data.code==200){
              //插入用户表
              console.log(89898989,total);
              if(total==0){
                let sql ="INSERT INTO  t_user(source,phone, uid, open_id, created_at) VALUES (?,?,?,?,?)";
                pool.query(sql, ["来伊份",phone,uid, openId ,new Date()],(err, result2) => {
                  res.send(utils.returnData({code:1,data:{uid:uid,phone:phone,openId:openId} }));
                });
              }else{
                let sql ="update t_user  set  open_id=?   where phone=?";
                pool.query(sql, [openId,phone],(err, result2) => {
                  let sql2 ="select uid FROM  t_user  where  phone=?";
                  pool.query(sql2, [phone],(err, result3) => {
                    console.log('uid');
                      uid=result3[0].uid
                      console.log(uid);
                      console.log('uid');
                      res.send(utils.returnData({code:1,data:{uid:uid,phone:phone,openId:openId} }));
                  });
                });
              }
            }else{
              console.log(data.msg);
              res.send(utils.returnData({code:0,data:data.msg }));
            }
        });  
    });  
      
    req.on('error', function (e) {  
        console.log('problem with request: ' + e.message);  
    });  
    req.end();
});
// router.get("/login", async (req, res) => {
//   let obj=req.query;
//   var phone=obj.phone;
//   var code=obj.code;
//   var openId=obj.openId;
//   var uid=uuidv4();
//   let {total}=await utils.getSum({name:"t_user",where:`WHERE phone  ="${phone}"`,res,req});
//   console.log(phone+code);
//   const retrievedCode = utils.retrievePhoneVerificationCode(phone);
//   console.log("retrievedCode:"+retrievedCode); 
//   if (retrievedCode === code) {
//       console.log(0,"验证码正确");
//       console.log(89898989,total);
//       if(total==0){
//         let sql ="INSERT INTO  t_user(source,phone, uid, open_id, created_at) VALUES (?,?,?,?,?)";
//         pool.query(sql, ["来伊份",phone,uid, openId ,new Date()],(err, result2) => {
//           res.send(utils.returnData({code:1,data:{uid:uid,phone:phone,openId:openId} }));
//         });
//       }else{
//         let sql ="update t_user  set  open_id=?   where phone=?";
//         pool.query(sql, [openId,phone],(err, result2) => {
//           console.log(1234567,result2);
//           let sql2 ="select uid FROM  t_user  where  phone=?";
//           pool.query(sql2, [phone],(err, result3) => {
//             console.log('uid');
//               uid=result3[0].uid
//               console.log(uid);
//               console.log('uid');
//               res.send(utils.returnData({code:1,data:{uid:uid,phone:phone,openId:openId} }));
//           });
//         });
//       }
//   } else {
//       console.log(1,"验证码错误");
//       res.send(utils.returnData({code:0,data:{} }));
//   }
// });

/**
 * @swagger
 * /syn/notices:
 *   post:
 *     tags:
 *       - 小程序
 *     summary: 消息提示
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
//  router.get("/notices", async (req, res) => {
//    let num=2
//   res.send(utils.returnData({data: num}));
// });



/**
 * @swagger
 * /syn/activitie/list:
 *   post:
 *     tags:
 *       - 小程序
 *     summary: 活动列表
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/activitie/list", async (req, res) => {


});



/**
 * @swagger
 * /syn/product/list:
 *   post:
 *     tags:
 *       - 小程序-权益
 *     summary: 权益列表
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/product/list", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let sql = `SELECT  id,sku_name,price,orinprice,detail1,detail2,detail3,detail4,detail5,detail6,detail7,detail8,inventory,giftType,sendType  FROM product  `;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});

router.post("/product/detailById", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let sql = `SELECT  id,sku_name,price,orinprice,detail1,detail2,detail3,detail4,detail5,detail6,detail7,detail8,inventory,giftType,sendType  FROM product  WHERE id = "${obj.id}" `;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
})















//小程序
// 地址
 router.post("/addressNew/list", async (req, res) => {
  let obj=req.body;
  let sql = `SELECT 
    id,
    province,
    city,
    phone,
    t_phone, 
    county,
    detailAddress,
    name,
    createdAt,
    updatedAt,
    add_type
FROM (
   
    SELECT
        a.id,
        a.province,
        a.city,
        a.phone,
        b.phone AS t_phone, 
        a.county,
        a.detail_address AS detailAddress,
        a.name,
        1 AS add_type,
        a.created_at AS createdAt,
        a.updated_at AS updatedAt
    FROM
        t_user_consignee a
    LEFT JOIN t_user b ON a.t_user_id = b.uid
    WHERE
        b.phone = ${obj.phone}  

    UNION ALL

    SELECT
        id,
        province,
        city,
        phone,
        ${obj.phone} AS t_phone,  
        county,
        detail_address AS detailAddress,
        name,
        2 AS add_type,
        created_at AS createdAt,
        updated_at AS updatedAt
    FROM
        t_address
    WHERE
        t_phone = ${obj.phone} 
) combined_results
ORDER BY updatedAt DESC;  `;
  pool.query(sql, [obj.phone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});

//地址新增或修改
 router.post("/addressNew/add", async (req, res) => {
  let obj=req.body;
  let sql = `INSERT INTO  t_address(province,city, phone, county,detail_address,name,created_at,updated_at,t_phone) VALUES (?,?,?,?,?,?,?,?,?) `;
  pool.query(sql, [obj.province,obj.city,obj.phone,obj.county,obj.detailAddress,obj.name,new Date(),new Date(),obj.userPhone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});
router.post("/addressNew/update", async (req, res) => {
  let obj=req.body;
  let sql = `update t_address set province=?,city=?, county=?,detail_address=?,name=?,phone=?,updated_at=?   WHERE id =? `;
  pool.query(sql, [obj.province,obj.city,obj.county,obj.detailAddress,obj.name,obj.phone,new Date(),obj.id],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});

 router.post("/addressNew/delete", async (req, res) => {
  let obj=req.body;
  let sql = `DELETE FROM  t_address   WHERE id =? `;
  pool.query(sql, [obj.id],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});
// 商品
router.post("/productNew/list", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let sql =`SELECT id, discount_type,discount,iscoupon,source, sku_id as skuId,iscoupon, sku_code as skuCode, sku_name as skuName, img_url as imgUrl,img_details as imgDetails,price,orinprice,inventory,type,state, create_time as createTime,ydurl,giftType,sendType  FROM product  WHERE  sku_id = '' and  FIND_IN_SET("${obj.type}", type) > 0  and state=1   ORDER BY iscoupon DESC,update_time DESC `;
  if(obj.type==0){
    sql = `SELECT id, discount_type,discount,iscoupon,source, sku_id as skuId,iscoupon, sku_code as skuCode, sku_name as skuName, img_url as imgUrl,img_details as imgDetails,price,orinprice,inventory,type,state, create_time as createTime,ydurl,giftType,sendType  FROM product  WHERE  sku_id = ''  and sku_name LIKE "%${obj.skuName||''}%"  and state=1 ORDER BY iscoupon DESC,update_time DESC `;
  }
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});
router.post("/productNew/detailById", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let sql = `SELECT id, discount_type,discount,iscoupon,source, sku_id as skuId,iscoupon, sku_code as skuCode, sku_name as skuName, img_url as imgUrl,img_details as imgDetails,price,orinprice,inventory,type,state, create_time as createTime,detailJson,ydurl  FROM product  WHERE id = "${obj.id}"   `;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});

//新增接口，根据giftType查询/productNew/list
router.post("/productNew/listByType", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let sql = `SELECT id, discount_type,discount,iscoupon,source, sku_id as skuId,iscoupon, sku_code as skuCode, sku_name as skuName, img_url as imgUrl,img_details as imgDetails,price,orinprice,inventory,type,state, create_time as createTime,ydurl,giftType,sendType  FROM product  WHERE giftType = "${obj.giftType}" and state=1  ORDER BY iscoupon DESC,update_time DESC `;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});

//新增接口，根据sendType查询/productNew/list，只取最新的6个数据
router.post("/productNew/listBySendType", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let sql = `SELECT id, discount_type,discount,iscoupon,source, sku_id as skuId,iscoupon, sku_code as skuCode, sku_name as skuName, img_url as imgUrl,img_details as imgDetails,price,orinprice,inventory,type,state, create_time as createTime,ydurl,giftType,sendType  FROM product  WHERE sendType !='' and state=1  ORDER BY iscoupon DESC,update_time DESC limit 6`;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});
//购买会员商品，赠送会员
router.post("/vip/buy", async (req, res) => {
  let obj=req.body;
  let vipName;
  if(obj.vipType==1){
    vipName="零食基础会员";
  }
  if(obj.vipType==2){
    vipName="零食精选会员";
  }
  if(obj.vipType==3){
    vipName="零食畅享会员";
  }
  if(obj.vipType==4){
    vipName="零食优选会员";
  }

  //判断当前用户是否购买过同类型的会员，如果购买过，就更新会员到期时间，增加30天
  let sql = `SELECT * FROM user_vip WHERE phone = "${obj.phone}" AND vip_type = "${obj.vipType}"`;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      if(result.length>0){
        //更新会员到期时间，增加30天
        let sql = `UPDATE user_vip SET vip_expiration_date = DATE_ADD(vip_expiration_date, INTERVAL 30 DAY) WHERE id = "${result[0].id}"`;
        pool.query(sql, [],(err, result) => {
          if (err) return res.send(utils.returnData({code: -1,err,req}));
          res.send(utils.returnData({data: result}));
        });
      }else{
        //新增会员
        let sql = `INSERT INTO user_vip (phone, vip_type,vip_name, create_time,vip_expiration_date) VALUES ("${obj.phone}", "${obj.vipType}","${vipName}", NOW(),DATE_ADD(NOW(), INTERVAL 30 DAY))`;
        pool.query(sql, [],(err, result) => {
          if (err) return res.send(utils.returnData({code: -1,err,req}));
          res.send(utils.returnData({data: result}));
        });
      }
      //不管是新增还是更新都要执行，会员送券方法
      utils.sendCouponToUser(obj.phone,obj.vipType);
  });

  console.log(99889,obj); 
  
});



//获取用户优惠券
 router.post("/couponsNum", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let sql = `SELECT 
    COUNT(*) AS activeCount
FROM
    user_coupon uc
INNER JOIN 
    product c ON uc.coupon_id = c.id
WHERE
    uc.phone = "${obj.phone}"
    AND (
        (c.discount_type IN (9,10,11,12) AND uc.expiration_date >= NOW())
        OR
        (c.discount_type IN (4,6,13,14,15,16) 
            AND uc.is_use != 1 
            AND DATE_ADD(uc.create_time, INTERVAL 30 DAY) >= NOW())
    ) `;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});
 router.post("/couponsList", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let sql = `SELECT
    uc.phone,
    uc.id AS id,
    CASE 
      WHEN uc.no_times = 1 THEN uc.expiration_date
      WHEN c.discount_type = 5 THEN DATE_ADD(uc.create_time, INTERVAL 7 DAY)
      ELSE DATE_ADD(uc.create_time, INTERVAL 30 DAY)
    END AS calculatedExpirationDate,
    uc.is_use,
    uc.no_times,
    c.id AS couponId,
    c.sku_name AS skuName,
    c.discount_type AS discountType,
    c.discount AS discount,
    c.detailJson,
    CASE 
      WHEN c.discount_type IN (9,10,11,12) THEN 
        CASE 
          WHEN uc.expiration_date < NOW() THEN '2'
          ELSE '0' 
        END
      WHEN c.discount_type IN (4,6,13,14,15,16) THEN 
        CASE 
          WHEN uc.is_use = 1 THEN '1' 
          WHEN DATE_ADD(uc.create_time, INTERVAL 30 DAY) < NOW() THEN '2' 
          ELSE '0' 
        END
      ELSE 
        CASE 
          WHEN uc.is_use = 1 THEN '1'
          WHEN DATE_ADD(uc.create_time, INTERVAL 7 DAY) < NOW() THEN '2' 
          ELSE '0' 
        END
    END AS STATUS
FROM
    user_coupon uc
    LEFT JOIN product c ON uc.coupon_id = c.id
  WHERE phone = "${obj.phone}"   and   c.discount_type IN  (4, 5, 6,9,10,11,12,13,14,15,16)   ORDER BY uc.create_time DESC`;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});



router.post("/couponsCountByStatus", async (req, res) => {
  let obj=req.body;
  let sql = `SELECT COUNT(*) AS count
FROM (
    SELECT
        uc.phone,
        CASE 
            WHEN c.discount_type IN (9,10,11,12) THEN 
                CASE 
                    WHEN uc.expiration_date < NOW() THEN '2'
                    ELSE '0' 
                END
            WHEN c.discount_type IN (4,6,13,14,15,16) THEN 
                CASE 
                    WHEN uc.is_use = 1 THEN '1' 
                    WHEN DATE_ADD(uc.create_time, INTERVAL 30 DAY) < NOW() THEN '2' 
                    ELSE '0' 
                END
            ELSE 
                CASE 
                    WHEN uc.is_use = 1 THEN '1'
                    WHEN DATE_ADD(uc.create_time, INTERVAL 7 DAY) < NOW() THEN '2' 
                    ELSE '0' 
                END
        END AS STATUS
    FROM
        user_coupon uc
        LEFT JOIN product c ON uc.coupon_id = c.id
    WHERE 
        uc.phone = "${obj.phone}"
        AND c.discount_type ="${obj.discountType}"
) AS coupon_status
WHERE STATUS = '0'`;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result[0]}));
  });
})




//插入用户优惠券
router.post("/addCouponForUser", async (req, res) => {
    let obj=req.body;
    console.log(99889,obj); 
    let sql = `INSERT INTO user_coupon (phone,coupon_id,expiration_date,is_use,update_time,create_time) VALUES(?,?,?,?,now(),now());`;
    pool.query(sql, [obj.phone,obj.couponId,obj.expirationDate,0],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});


























/**
 * @swagger
 * /syn/product/detail:
 *   post:
 *     tags:
 *       - 小程序-权益
 *     summary: 权益详情
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/product/detail", async (req, res) => {
    let obj=req.body;
    console.log(99889,obj); 
    let sql = `SELECT  sku_name,price,orinprice,detail1,detail2,detail3,detail4,detail5,detail6,detail7,detail8  FROM product  WHERE sku_code = "${obj.skuCode}" `;
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});


/**
 * @swagger
 * /syn/order/list:
 *   post:
 *     tags:
 *       - 小程序-订单
 *     summary: 订单列表
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/order/list", async (req, res) => {
  let obj=req.body;
  let sql = `SELECT a.id, a.source, a.order_id as orderId, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time as updateTime, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address FROM t_order a left join t_user_consignee b  on a.user_consignee_id=b.id WHERE a.phone ="${obj.phone}"  and a.source='来伊份'  and (a.state=1 or a.state=2 )  and a.sku_code like 'lyf0%'   ORDER BY a.update_time DESC `;
  pool.query(sql, [obj.phone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});

/**
 * @swagger
 * /syn/items:
 *   post:
 *     tags:
 *       - 小程序-我的
 *     summary: 待消费信息
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/items", async (req, res) => {
  let obj=req.body;
  let sql = `SELECT count(1) as num from t_order WHERE phone =? and state=0 and  sku_code like 'lyf0%'`;
  pool.query(sql, [obj.phone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});


/**
 * @swagger
 * /syn/redemption/list:
 *   post:
 *     tags:
 *       - 小程序-我的
 *     summary: 兑换券列表
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/redemption/list", async (req, res) => {
  let obj=req.body;
  let sql = `SELECT a.id, a.source, a.order_id as orderId, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,b.name AS consigneeName,b.phone AS consigneePhone,CONCAT(b.county, b.city, b.province,b.detail_address) AS  address FROM t_order a left join t_user_consignee b  on a.user_consignee_id=b.id WHERE a.phone ="${obj.phone}"  and a.source='来伊份'  and  a.state != 9   and  sku_code like 'lyf0%'  ORDER BY a.create_time DESC `;
  pool.query(sql, [obj.phone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});

/**
 * @swagger
 * /syn/redemption/receive:
 *   post:
 *     tags:
 *       - 小程序-我的
 *     summary: 兑换券领取
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/redemption/receive", async (req, res) => {
  let obj=req.body;
  //修改订单状态
  let sql = `update t_order  set receive_time=?,user_consignee_id=?,user_consignee_address=?,user_consignee_phone=?,user_consignee_name=?,express_company=?,state=?,update_time=?   WHERE id ="${obj.id}" `;
  pool.query(sql, [new Date(),obj.consigneeId,obj.consigneeAddress,obj.consigneePhone,obj.consigneeName,'',1,new Date()],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});

/**
 * @swagger
 * /syn/consignee/list:
 *   post:
 *     tags:
 *       - 小程序-我的
 *     summary: 地址列表
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/consignee/list", async (req, res) => {
  let obj=req.body;
  let sql = `
  SELECT
        a.id,
        a.province,
        a.city,
        a.phone,
        b.phone AS t_phone, 
        a.county,
        a.detail_address AS detailAddress,
        a.name,
        a.created_at AS createdAt,
        a.updated_at AS updatedAt
    FROM
        t_user_consignee a
    LEFT JOIN t_user b ON a.t_user_id = b.uid
  WHERE b.phone ="${obj.phone}"     ORDER BY a.updated_at DESC `;
  pool.query(sql, [obj.phone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});


//地址新增或修改
 router.post("/consignee/add", async (req, res) => {
  let obj=req.body;
  let sql = `INSERT INTO  t_user_consignee(province,city, phone, county,detail_address,name,created_at,updated_at,t_user_id) VALUES (?,?,?,?,?,?,?,?,?) `;
  pool.query(sql, [obj.province,obj.city,obj.phone,obj.county,obj.detailAddress,obj.name,new Date(),new Date(),obj.uid],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});
router.post("/consignee/update", async (req, res) => {
  let obj=req.body;
  let sql = `update t_user_consignee set province=?,city=?, county=?,detail_address=?,name=?,phone=?,updated_at=?   WHERE id =? `;
  pool.query(sql, [obj.province,obj.city,obj.county,obj.detailAddress,obj.name,obj.phone,new Date(),obj.id],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});

/**
 * @swagger
 * /syn/consignee/delete:
 *   post:
 *     tags:
 *       - 小程序-我的
 *     summary: 地址删除
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
 router.post("/consignee/delete", async (req, res) => {
  let obj=req.body;
  let sql = `DELETE FROM  t_user_consignee   WHERE id =? `;
  pool.query(sql, [obj.id],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });

});



//退出登录
router.post("/login/loginOut", async (req, res) => {
  let obj=req.body;
  let sql = `update t_user  set open_id=''  WHERE phone =? `;
  pool.query(sql, [obj.phone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: {}}));
  });

});


//拉取历史订单的接口
router.get('/getLaiyifenOrder',async (req,res) => {
  let datetime=utils.getDateTime();
    console.log(datetime+':手动拉取历史订单任务执行中...');
      var page=1;
  var bigData=[];
  while (true) {
    let result =await utils.getLaiyifenOrder('2023-08-24',page);
    bigData= bigData.concat(result);
    // 检查条件是否满足，如果满足则退出循环
    if (result.length<50) {
      console.log('条件满足，退出循环');
      break;
    }
    page++;
  }  
  console.log(bigData.length);
  for(let i=0;i<bigData.length;i++){
    console.log("次数=",i);
    let  bodyJson = bigData[i];
    let phone=bodyJson.serverNum;
    console.log(999,phone);
    //查询用户
    let userId=await utils.getTUserIdByCorn({phone:phone});

    //查询产品Id
    let skuId=bodyJson.skuId;
    let skuCode=bodyJson.skuCode;
    let skuName=bodyJson.skuName;
    let productId=await utils.getProductIdByCorn({skuId,skuCode,skuName});


    let orderId=bodyJson.orderItemId;
    let extCode=bodyJson.extCode;
    let quantity=bodyJson.quantity;
    let rightsId=bodyJson.rightsId;
    let price=bodyJson.price;
    let detailAddress=bodyJson.recipientAddress;

    let recipientNumber=bodyJson.recipientNumber;
    let receiveTime;
    if(bodyJson.receiveTime==null ||  bodyJson.receiveTime==''){
      receiveTime=null;
    }else{
      receiveTime= dayjs(Number(bodyJson.receiveTime)).format('YYYY-MM-DD HH:mm:ss');
    }
    let trackingNumber=bodyJson.trackingNumber;
    let state=0;
    if(recipientNumber!='' && recipientNumber!=null){
      trackingNumber!=''  && trackingNumber!=null?state=2:state=1
    }
    let createTime = dayjs(Number(bodyJson.createTime)).format('YYYY-MM-DD HH:mm:ss')
    console.log(999,createTime);
    let order_serial='';
    for (let i = 0; i < 12; i++) {
      order_serial += Math.floor(Math.random() * 10); // 生成0到9之间的随机数
    }
    let orderRes=await utils.getSumByCorn({name:"t_order",where:`WHERE order_id="${orderId}"`});
    console.log("本次要插入的历史订单");
    console.log(bodyJson);
    console.log("本次要插入的历史订单");
    if(state==0){
      console.log("插入状态为0的订单");
      //未领取
      //插入订单-判断订单是否存在，不存在就新增，存在不做操作
      if(orderRes.total==0){
        let sql ="INSERT INTO  t_order(source, order_id, phone, receive_time, ext_code, quantity,rights_id, price, product_id, sku_id, sku_code, sku_name, user_id, user_consignee_id, express_company, tracking_number, state, sms_num, create_time, update_time, created_at,order_serial) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        pool.query(sql, ["来伊份", orderId, phone, null,extCode, quantity,rightsId,price, productId, skuId, skuCode, skuName, userId, 0, '', '', 0, 0, createTime,new Date(),new Date(),order_serial],(err, result) => {
            if (err) console.log(err);
            // let sql2 ="INSERT INTO  t_order_sms(t_order_id, phone, type, status, result, created_at,updated_at) VALUES (?,?,?,?,?,?,?)";
            // pool.query(sql2, [orderId, phone,1,200,200,new Date(),new Date()],(err2, result2) => {
                // if (err2) console.log(err2);
            // });
        });
      }

    }else{
      console.log("插入状态不为0的订单");
      //已发货、已领取
      //先处理地址
      const components =utils.extractAddressComponents(detailAddress);
      console.log(components);
      let province=components.province;
      let city=components.city;
      let county=components.district;
      let address=components.details;
      let name=bodyJson.recipient;
      let totalData=await utils.getSumByCorn({name:"his_address",where:`WHERE order_id="${orderId}"`});
      console.log(123,totalData);
      //判断订单是否存在
      if(orderRes.total==0){
        //订单不存在
        console.log("插入状态不为0的新订单");
        let sql4 = `INSERT INTO  his_address(order_id,origin_address) VALUES (?,?) `;
        pool.query(sql4, [orderId,detailAddress],(err4, result4) => {
            if (err4) console.log(err4);
            console.log(333,result4);
            let sql3 = `INSERT INTO  t_user_consignee(province,city, phone, county,detail_address,name,created_at,updated_at,t_user_id) VALUES (?,?,?,?,?,?,?,?,?) `;
            pool.query(sql3, [province,city,recipientNumber,county,address,name,new Date(),new Date(),userId],(err3, result3) => {
                if (err3) console.log(err3);
                console.log(12345678);
                console.log(90,result3.insertId);
                let sql ="INSERT INTO  t_order(source, order_id, phone, receive_time, ext_code, quantity,rights_id, price, product_id, sku_id, sku_code, sku_name, user_id, user_consignee_id,user_consignee_address ,user_consignee_phone,user_consignee_name, express_company, tracking_number, state, sms_num, create_time, update_time, created_at,order_serial) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                pool.query(sql, ["来伊份", orderId, phone, receiveTime,extCode, quantity,rightsId,price, productId, skuId, skuCode, skuName, userId, result3.insertId,detailAddress,recipientNumber,name, '', trackingNumber?trackingNumber:'', state, 0, createTime,new Date(),new Date(),order_serial],(err, result) => {
                  if (err) console.log(err);
                    console.log(000,result);
                    // let sql2 ="INSERT INTO  t_order_sms(t_order_id, phone, type, status, result, created_at,updated_at) VALUES (?,?,?,?,?,?,?)";
                    // pool.query(sql2, [orderId, phone,1,200,200,new Date(),new Date()],(err2, result2) => {
                    //     if (err2) if (err2) console.log(err2);
                    // });
                });
            });
        });
      }else{
        if(totalData.total==0){
          console.log("更新状态不为0的存在的订单，但地址不存在");
          let sql = `INSERT INTO  his_address(order_id,origin_address) VALUES (?,?) `;
          pool.query(sql, [orderId,detailAddress],(err, result) => {
            if (err) console.log(err);
              let sql3 = `INSERT INTO  t_user_consignee(province,city, phone, county,detail_address,name,created_at,updated_at,t_user_id) VALUES (?,?,?,?,?,?,?,?,?) `;
              pool.query(sql3, [province,city,recipientNumber,county,address,name,new Date(),new Date(),userId],(err3, result3) => {
                if (err3) console.log(err3);
                  console.log(3333,result3);
                  let sql2 = `update t_order  set receive_time=?,user_consignee_id=?,user_consignee_address=?,user_consignee_phone=?,user_consignee_name=?,express_company=?,tracking_number=?,state=?,update_time=?   WHERE order_id ="${orderId}" `;
                  pool.query(sql2, [receiveTime,result3.insertId,detailAddress,recipientNumber,name,'',trackingNumber?trackingNumber:'',state,new Date()],(err2, result2) => {
                      if (err2) console.log(err2);
                  });
              });
          });
        }else{
          //地址表有记录，
          console.log("更新状态不为0的存在的订单，而且地址存在");
          // let consigneeId=await utils.getConsigneeIdByCorn({province,city,recipientNumber,county,address,name});
          // console.log("consigneeId="+consigneeId);
          let sql2 = `update t_order  set receive_time=?,user_consignee_id=?,user_consignee_address=?,user_consignee_phone=?,user_consignee_name=?,express_company=?,tracking_number=?,state=?,update_time=?   WHERE order_id ="${orderId}" `;
          pool.query(sql2, [receiveTime,0,detailAddress,recipientNumber,name,'',trackingNumber?trackingNumber:'',state,new Date()],(err2, result2) => {
            if (err2) console.log(err2);
              console.log(22,result2);
          });
        }
      }
    }
  }
  res.send(utils.returnData({data: {}}));
  
})


//推送短信状态
router.post("/messages/push", async (req, res) => {
  console.log("223434");
  let obj=req.body;
  console.log("收到推送");
  if(obj.length>0){
    for(let i=0;i<obj.length;i++){
      if(obj[i].status=='DELIVRD'){
        let sql2 ="update t_order  set  massage_status=200  where  msgId=?";
        pool.query(sql2, [obj[i].msgId],(err2, result2) => {
            if (err2) return res.send(utils.returnData({code: -1,err2,req}));
            res.send(utils.returnData({data: result2}));
        });
      }else{
        let sql2 ="update t_order  set  massage_status=0  where  msgId=?";
        pool.query(sql2, [obj[i].msgId],(err2, result2) => {
            if (err2) return res.send(utils.returnData({code: -1,err2,req}));
            res.send(utils.returnData({data: result2}));
        });
      }
    }
    
  }
  
  //obj
  //
//   [
// {
//   msgId: 896120462,
//   phone: '13957177080',
//   receiveTime: '2024-06-02 21:43:02',
//   smsCount: 1,
//   status: 'DELIVRD'
//   }
//   ]
 console.log(obj);

});
//推送短信状态
router.post("/upstream/push", async (req, res) => {
  let obj=req.body;
  console.log("收到上行消息");
 console.log(obj);

});



//购物车
router.post("/addBuyCar", async (req, res) => {
  let obj=req.body;
  console.log(99889,obj); 
  let {total}=await utils.getSum({name:"goods_car",where:`WHERE phone="${obj.phone}" and product_id ="${obj.productId}" `,res,req});
  let sql ='';
  if(total>0){
    sql = `UPDATE  goods_car  SET  buyNum=buyNum + ?  where phone=? and product_id=?`;
    pool.query(sql, [obj.buyNum,obj.phone,obj.productId],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
  }else{
    sql = `INSERT INTO goods_car (phone,product_id,buyNum,isSelected,create_time) VALUES(?,?,?,?,now());`;
    pool.query(sql, [obj.phone,obj.productId,obj.buyNum,0],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
  }
  
});
//获取商品库存
router.post("/getProInventory", async (req, res) => {
  let sql = "SELECT inventory  FROM product WHERE id=?", obj = req.body;
  pool.query(sql, [obj.id], (err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});
router.post("/delBuyCar", async (req, res) => {
    let sql = "DELETE FROM goods_car WHERE id=?", obj = req.body;
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//修改购物车数据
router.post("/upBuyCar", async (req, res) => {
  let obj=req.body;
  console.log(668868,obj); 
  let sql = `UPDATE  goods_car  SET  buyNum=?,isSelected=? where id=?;`;
  pool.query(sql, [obj.buyNum,obj.isSelected,obj.id],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});

router.post("/getBuyCarList", async (req, res) => {
  let obj=req.body;
  let sql = `
  SELECT 
    gc.id as id,p.id AS productId,p.iscoupon,p.sku_name as skuName, p.img_url as imgUrl,p.discount_type as discountType,p.price,p.orinprice,p.inventory,p.type,p.state,gc.create_time as createTime,
    gc.buyNum, 
    gc.isSelected 
FROM 
    goods_car AS gc 
INNER JOIN 
    product AS p 
ON 
    gc.product_id = p.id 
WHERE 
    gc.phone =  "${obj.phone}"   ORDER BY gc.create_time DESC `;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});
router.post("/getBuyCarNum", async (req, res) => {
  let obj=req.body;
  let sql = `select count(*) as num from  goods_car  WHERE phone =  "${obj.phone}" `;
  pool.query(sql, [],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: result}));
  });
});

  //测试
  router.get('/test', (req,res) => {
  //   let obj=req.query;
  // let vipName;
  // if(obj.vipType==1){
  //   vipName="基础会员";
  // }
  // if(obj.vipType==2){
  //   vipName="精选会员";
  // }
  // if(obj.vipType==3){
  //   vipName="畅享会员";
  // }
  // if(obj.vipType==4){
  //   vipName="优选会员";
  // }

  // //判断当前用户是否购买过同类型的会员，如果购买过，就更新会员到期时间，增加30天
  // let sql = `SELECT * FROM user_vip WHERE phone = "${obj.phone}" AND vip_type = "${obj.vipType}"`;
  // pool.query(sql, [],(err, result) => {
  //     if (err) return res.send(utils.returnData({code: -1,err,req}));
  //     if(result.length>0){
  //       //更新会员到期时间，增加30天
  //       let sql = `UPDATE user_vip SET vip_expiration_date = DATE_ADD(vip_expiration_date, INTERVAL 30 DAY) WHERE id = "${result[0].id}"`;
  //       pool.query(sql, [],(err, result) => {
  //         if (err) return res.send(utils.returnData({code: -1,err,req}));
  //         res.send(utils.returnData({data: result}));
  //       });
  //     }else{
  //       //新增会员
  //       let sql = `INSERT INTO user_vip (phone, vip_type,vip_name, create_time,vip_expiration_date) VALUES ("${obj.phone}", "${obj.vipType}","${vipName}", NOW(),DATE_ADD(NOW(), INTERVAL 30 DAY))`;
  //       pool.query(sql, [],(err, result) => {
  //         if (err) return res.send(utils.returnData({code: -1,err,req}));
  //         res.send(utils.returnData({data: result}));
  //       });
  //     }
  //     //不管是新增还是更新都要执行，会员送券方法
  //     utils.sendCouponToUser(obj.phone,obj.vipType);
  // });

  // console.log(99889,obj); 
  
    // const orderExample = {
    //                 orderId: "LIFE10000820250911171707abuU",
    //                 isPlatformOrder: true,
    //                 payOrg: "HFPAY",
    //                 payWay: 0,
    //                 payType: 2,
    //                 payTime: new Date(),
    //                 amount: 0,
    //                 spuId:'70',
    //                 spuName:'88折商城优惠券',
    //               };
    //               执行推送
    //               pushPaymentStatus(orderExample)
    //                 .then(result => console.log("推送结果:", result))
    //                 .catch(err => console.error("执行失败:", err));
    // pushClearanceStatus({
    //                         orderItemId: "1417905523027107841",
    //                         serverNum: "13527829527",
    //                         skuCode:  "0415515795",
    //                         skuName: "零食基础会员",
    //                         clearanceResult: 1, // 1-核销成功
    //                         couponId: "115"
    //                       });

    utils.sedNewHttpMessageByVip('13957177080','零食基础会员');
    res.send({
        msg:'测试专用',
        data:{time:"",data:{}}
    })
  })

  //新订单插入
router.post("/addNewOrder", (req, res) => {
    console.log(req.body);
    let obj=req.body;
    let sql = "INSERT INTO t_new_order(order_id,phone,pay_time,total_price,total_discount,total_fee,coupon_id,goods_json,user_consignee_id,user_consignee_name,user_consignee_phone,user_consignee_address,state,update_time,create_time,goodsIsOnlyCoupon,giftName,giftUrl) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now(),?,?,?)";

    pool.query(sql, [obj.orderId,obj.phone,obj.payTime,obj.totalPrice,obj.totalDiscount,obj.totalFee,obj.couponId,obj.goodsJson,obj.consigneeId,obj.consigneeName,obj.consigneePhone,obj.currentAddress,0,obj.isOnlyCoupon,obj.giftName,obj.giftUrl], (err, result) => {

        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: ''}));
    });
});
router.post("/addNewOrderSuc", (req, res) => {
    console.log(req.body);
    let obj=req.body;
    let sql = "INSERT INTO t_new_order(order_id,phone,pay_time,total_price,total_discount,total_fee,coupon_id,goods_json,user_consignee_id,user_consignee_name,user_consignee_phone,user_consignee_address,state,update_time,create_time,goodsIsOnlyCoupon) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now(),?)";
    pool.query(sql, [obj.orderId,obj.phone,obj.payTime,obj.totalPrice,obj.totalDiscount,obj.totalFee,obj.couponId,obj.goodsJson,obj.consigneeId,obj.consigneeName,obj.consigneePhone,obj.currentAddress,1,obj.isOnlyCoupon], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: ''}));
    });
});

  //新订单查询
router.post("/getNewOrder", (req, res) => {
    console.log(req.body);
    let obj=req.body;
    let sql = `SELECT 
    deleted,
    giftName,
    giftUrl,
  id, source, orderId, phone, 
  receiveTime, payTime, extCode, quantity, rightsid, price, 
  skuId, skuCode, skuName, totalPrice, totalDiscount, totalFee, 
  couponId, goodsJson, user_consignee_id, express_company, trackingNumber, 
  state, sms_num, createTime, updateTime, created_at, consigneeName, 
  consigneePhone, address, order_serial, msgId, massage_status, msgNum,
  order_type  -- 新增的区分字段
FROM (
  SELECT 
    '' AS deleted,
    '' AS giftName,
    '' AS giftUrl,
    a.id, 
    a.source, 
    a.order_id AS orderId, 
    a.phone, 
    a.receive_time AS receiveTime, 
    NULL AS payTime,
    a.ext_code AS extCode, 
    a.quantity, 
    a.rights_id AS rightsid, 
    a.price, 
    a.sku_id AS skuId, 
    a.sku_code AS skuCode, 
    a.sku_name AS skuName, 
    NULL AS totalPrice,
    NULL AS totalDiscount,
    NULL AS totalFee,
    NULL AS couponId,
    NULL AS goodsJson,
    a.user_consignee_id, 
    a.express_company, 
    a.tracking_number AS trackingNumber, 
    a.state, 
    a.sms_num, 
    a.create_time AS createTime, 
    a.update_time AS updateTime, 
    a.created_at,
    a.user_consignee_name AS consigneeName,
    a.user_consignee_phone AS consigneePhone,
    a.user_consignee_address AS address,
    NULL AS order_serial,
    NULL AS msgId,
    NULL AS massage_status,
    NULL AS msgNum,
    1 AS order_type  -- 标记为t_order数据
  FROM t_order a
  LEFT JOIN t_user_consignee b ON a.user_consignee_id = b.id 
  WHERE a.phone = "${obj.phone}"
    AND a.source = '来伊份'
    AND (a.state = 1 OR a.state = 2)
    AND a.sku_code LIKE 'lyf0%'
  
  UNION ALL
  
  SELECT 
  deleted,
    giftName,
    giftUrl,
    id, 
    source, 
    order_id AS orderId, 
    phone, 
    NULL AS receiveTime,
    pay_time AS payTime,
    NULL AS extCode, 
    NULL AS quantity, 
    NULL AS rightsid, 
    NULL AS price, 
    NULL AS skuId, 
    NULL AS skuCode, 
    NULL AS skuName, 
    total_price AS totalPrice,
    total_discount AS totalDiscount,
    total_fee AS totalFee,
    coupon_id AS couponId,
    goods_json AS goodsJson,
    user_consignee_id, 
    express_company, 
    tracking_number AS trackingNumber, 
    state, 
    sms_num, 
    create_time AS createTime, 
    update_time AS updateTime, 
    NULL AS created_at,
    user_consignee_name AS consigneeName,
    user_consignee_phone AS consigneePhone,
    user_consignee_address AS address,
    order_serial,
    msgId,
    massage_status,
    msgNum,
    2 AS order_type  -- 标记为t_new_order数据
  FROM t_new_order 
  WHERE phone = "${obj.phone}"
    AND deleted IS null
) AS combined_orders
ORDER BY updateTime DESC`;
    pool.query(sql, [], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getOldOrderById", (req, res) => {
    console.log(req.body);
    let obj=req.body;
    let sql = `SELECT
      id,
      source,
      sku_name AS skuName, 
      sku_code AS skuCode, 
      1 AS order_type,
      order_id AS orderId,
      phone,
      quantity, 
      receive_time AS receiveTime,
      null AS totalPrice,
      null AS totalDiscount,
      null AS totalFee,
      null AS couponId,
      null AS goodsJson,
      user_consignee_id,
      express_company,
      tracking_number,
      state,
      sms_num,
      update_time,
      create_time,
      order_serial,
      user_consignee_address,
      user_consignee_phone,
      user_consignee_name,
      msgId,
      massage_status,
      msgNum
    FROM
      t_order
      where id ="${obj.id}" 
    ORDER BY update_time desc`;
    pool.query(sql, [], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
router.post("/getNewOrderById", (req, res) => {
    console.log(req.body);
    let obj=req.body;
    let sql = `SELECT
    giftName,
    giftUrl,
      id,
      source,
      2 AS order_type,
      order_id AS orderId,
      phone,
      pay_time AS payTime,
      total_price AS totalPrice,
      total_discount AS totalDiscount,
      total_fee AS totalFee,
      coupon_id AS couponId,
      goods_json AS goodsJson,
      user_consignee_id,
      express_company,
      tracking_number,
      state,
      sms_num,
      update_time,
      create_time,
      order_serial,
      user_consignee_address,
      user_consignee_phone,
      user_consignee_name,
      msgId,
      massage_status,
      msgNum
    FROM
      t_new_order
      where id ="${obj.id}" 
    ORDER BY update_time desc`;
    pool.query(sql, [], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
router.post("/getGoosByOrederId", (req, res) => {
    console.log(req.body);
    let obj=req.body;
    let sql = `SELECT
      goods_json AS goodsJson
    FROM
      t_new_order
      where order_id ="${obj.orderId}" `;
    pool.query(sql, [], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
//更改订单状态
router.post("/updateNewOrder", async (req, res) => {
  let obj=req.body;
  let argus=[obj.state,obj.orderId];
  let sql = `update t_new_order  set state=?  WHERE order_id =? `;
  if(obj.state==9){
    sql = `update t_new_order  set deleted=1  WHERE order_id =? `;
    argus=[obj.orderId];
  }
  console.log('-----');
  console.log(sql);
  console.log('-----');
  pool.query(sql, argus,(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: {}}));
  });

});

//减少库存
router.post("/updateInventory", async (req, res) => {
  let obj=req.body;
  let sql = `update product  set inventory=?  WHERE id =? `;
  pool.query(sql, [obj.inventory,obj.id],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: {}}));
  });

});
//quit库存
router.post("/quitInventory", async (req, res) => {
  let obj=req.body;
  let sql = `update product  set inventory=inventory+?  WHERE id =? `;
  pool.query(sql, [obj.buyNum,obj.id],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: {}}));
  });

});
//删除购物车
router.post("/removeCar", async (req, res) => {
  let obj=req.body;
  const placeholders = obj.ids.map(() => '?').join(',');
  const params = [...obj.ids, obj.phone];
  let sql = `DELETE FROM goods_car WHERE id IN (${placeholders}) AND phone = ?`;
  pool.query(sql, params,(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: {}}));
  });

});

router.post("/userCoupon", async (req, res) => {
  let obj=req.body;
  let sql = `update user_coupon  set is_use=1  WHERE id =?  and phone=?`;
  pool.query(sql, [obj.id,obj.phone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: {}}));
  });

});

router.post("/backCoupon", async (req, res) => {
  let obj=req.body;
  let sql = `update user_coupon  set is_use=0  WHERE id =?  and phone=?`;
  pool.query(sql, [obj.id,obj.phone],(err, result) => {
      if (err) return res.send(utils.returnData({code: -1,err,req}));
      res.send(utils.returnData({data: {}}));
  });

});






module.exports = router;
